"""add shipment fields and sys_config table

Revision ID: 002
Revises: 001
Create Date: 2024-01-01 00:00:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
import datetime

# revision identifiers, used by Alembic.
revision = '002'
down_revision = '001'
branch_labels = None
depends_on = None


def upgrade():
    # 为 shipment_monitor 表添加新字段
    op.add_column('shipment_monitor', sa.Column('pickup_warehouse_coordinates', sa.String(100), comment='取货仓库位置坐标'))
    op.add_column('shipment_monitor', sa.Column('delivery_warehouse_coordinates', sa.String(100), comment='卸货仓库位置坐标'))
    op.add_column('shipment_monitor', sa.Column('start_time', sa.DateTime, comment='开始时间'))
    op.add_column('shipment_monitor', sa.Column('end_time', sa.DateTime, comment='结束时间'))
    op.add_column('shipment_monitor', sa.Column('status', sa.Integer, default=0, comment='状态：0-未开始，10-进行中，30-完成，-10-异常'))
    op.add_column('shipment_monitor', sa.Column('execution_info', sa.Text, comment='执行信息'))

    # 创建 sys_config 表
    op.create_table('sys_config',
        sa.Column('id', sa.Integer(), nullable=False, autoincrement=True, primary_key=True),
        sa.Column('code', sa.String(length=64), nullable=False, unique=True, comment='唯一代码'),
        sa.Column('value', sa.Text, comment='配置值'),
        sa.Column('remarks', sa.String(length=500), comment='备注'),
        sa.Column('create_by', sa.String(length=64), comment='创建人'),
        sa.Column('create_time', sa.DateTime, default=datetime.datetime.now, comment='创建时间'),
        sa.Column('update_by', sa.String(length=64), comment='修改人'),
        sa.Column('update_time', sa.DateTime, onupdate=datetime.datetime.now, comment='修改时间'),
        sa.Column('version', sa.Integer, default=1, comment='版本号'),
        mysql_engine='InnoDB',
        mysql_charset='utf8mb4'
    )
    
    # 创建索引
    op.create_index('idx_sys_config_code', 'sys_config', ['code'])
    op.create_index('idx_shipment_status', 'shipment_monitor', ['status'])


def downgrade():
    # 删除 sys_config 表
    op.drop_index('idx_sys_config_code', table_name='sys_config')
    op.drop_table('sys_config')
    
    # 删除 shipment_monitor 表的新字段
    op.drop_index('idx_shipment_status', table_name='shipment_monitor')
    op.drop_column('shipment_monitor', 'execution_info')
    op.drop_column('shipment_monitor', 'status')
    op.drop_column('shipment_monitor', 'end_time')
    op.drop_column('shipment_monitor', 'start_time')
    op.drop_column('shipment_monitor', 'delivery_warehouse_coordinates')
    op.drop_column('shipment_monitor', 'pickup_warehouse_coordinates')